﻿//-- =============================================
//-- Author:	    Amir
//-- Create date:   1390/1/23
//-- Description:	PayAndReceive Data Access Layer Class
//-- E-Mail:    	amsjavan@gmail.com
//-- Mobile:        0935-527-6131
//-- Last Update:   ---
//-- =============================================

#define DEBUG



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace ChequeManagement.DAL
{
    /// <summary>
    /// this class contain methods for connect to dataBase 
    /// this methods are Insert,Update,FetchAll,Delete,InitialObjectsFromReader,InitialBusinessObjectsFromReader
    /// this class inheritance of BaseDAL
    /// </summary>
    public class DALPayAndReceive : BaseDAL
    {
        /// <summary>
        /// this constructor called base constructor and set parameter
        /// parameter value is Connectionstring for connect to dataBase
        /// </summary>
        public DALPayAndReceive()
            : base(ChequeManagement.Properties.Settings.Default.Database1ConnectionString)
        {
        }

        /// <summary>
        /// this method is for insert a object of BLL.BLLBank to table
        /// this method check connection if connection is open insert object to table
        /// </summary>
        /// <param name="payAndReceiveObject" Type="BLL.BLLPayAndReceive"></param>
        /// <return>this type is void and Do not return</return>
        public void Insert(BLL.BLLPayAndReceive payAndReceiveObject)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveInsert";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();


                SqlParameter pkPayAndReceiveID = new SqlParameter();
                pkPayAndReceiveID.ParameterName = "@pkPayAndReceiveID";
                pkPayAndReceiveID.SqlDbType = SqlDbType.Int;
                pkPayAndReceiveID.Direction = ParameterDirection.Output;
                pkPayAndReceiveID.IsNullable = false;
                this.Command.Parameters.Add(pkPayAndReceiveID);

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Input;
                drawerID.IsNullable = false;
                drawerID.Value = payAndReceiveObject.DrawerObj.DrawerID;
                this.Command.Parameters.Add(drawerID);

                SqlParameter date = new SqlParameter();
                date.ParameterName = "@date";
                date.SqlDbType = SqlDbType.DateTime;
                date.Direction = ParameterDirection.Input;
                date.IsNullable = false;
                date.Value = payAndReceiveObject.Date.ToString();
                this.Command.Parameters.Add(date);

                SqlParameter description = new SqlParameter();
                description.ParameterName = "@description";
                description.SqlDbType = SqlDbType.NVarChar;
                description.Direction = ParameterDirection.Input;
                description.IsNullable = true;
                description.Value = payAndReceiveObject.Description;
                this.Command.Parameters.Add(description);

                SqlParameter type = new SqlParameter();
                type.ParameterName = "@type";
                type.SqlDbType = SqlDbType.SmallInt;
                type.Direction = ParameterDirection.Input;
                type.IsNullable = false;
                type.Value = (int)payAndReceiveObject.Type;
                this.Command.Parameters.Add(type);


                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                payAndReceiveObject.PayAndReceiveID = Convert.ToInt32(this.Command.Parameters["@pkPayAndReceiveID"].Value);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Update a object of BLL.BLLBank to table by PayAndReceiveID
        /// this method check connection if connection is open Update object to table
        /// </summary>
        /// <param name="payAndReceiveObject" Type="BLL.BLLPayAndReceive"></param>
        /// <param name="targetpayAndReceiveID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Update(BLL.BLLPayAndReceive payAndReceiveObject, int targetpayAndReceiveID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveUpdate";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkPayAndReceiveID = new SqlParameter();
                pkPayAndReceiveID.ParameterName = "@pkPayAndReceiveID";
                pkPayAndReceiveID.SqlDbType = SqlDbType.Int;
                pkPayAndReceiveID.Direction = ParameterDirection.Input;
                pkPayAndReceiveID.IsNullable = false;
                pkPayAndReceiveID.Value = targetpayAndReceiveID;
                this.Command.Parameters.Add(pkPayAndReceiveID);

                SqlParameter drawerID = new SqlParameter();
                drawerID.ParameterName = "@drawerID";
                drawerID.SqlDbType = SqlDbType.Int;
                drawerID.Direction = ParameterDirection.Input;
                drawerID.IsNullable = false;
                drawerID.Value = payAndReceiveObject.DrawerObj.DrawerID;
                this.Command.Parameters.Add(drawerID);

                SqlParameter date = new SqlParameter();
                date.ParameterName = "@date";
                date.SqlDbType = SqlDbType.DateTime;
                date.Direction = ParameterDirection.Input;
                date.IsNullable = false;
                date.Value = payAndReceiveObject.Date.ToString();
                this.Command.Parameters.Add(date);

                SqlParameter description = new SqlParameter();
                description.ParameterName = "@description";
                description.SqlDbType = SqlDbType.NVarChar;
                description.Direction = ParameterDirection.Input;
                description.IsNullable = true;
                description.Value = payAndReceiveObject.Description;
                this.Command.Parameters.Add(description);

                SqlParameter type = new SqlParameter();
                type.ParameterName = "@type";
                type.SqlDbType = SqlDbType.SmallInt;
                type.Direction = ParameterDirection.Input;
                type.IsNullable = false;
                type.Value = (int)payAndReceiveObject.Type;
                this.Command.Parameters.Add(type);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="payAndReceiveDT" Type="DataTable"></param>
        /// <return>this type is void but result return to DataTable </return>
        public void FetchAll(DataTable payAndReceiveDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspPayAndReceiveFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                sqlDataAdapter.Fill(payAndReceiveDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <returns>result return to a list<> of ChequeManagement.BLL.BLLPayAndReceive</returns>
        public List<ChequeManagement.BLL.BLLPayAndReceive> FetchAll()
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspPayAndReceiveFetchAll";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                
                List<BLL.BLLPayAndReceive> result = new List<BLL.BLLPayAndReceive>();

                //we must close the datareader because if we have a exception and the roolBack happen it will cause an error.
                //because it cannat roolback if a dataReader was open
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <returns>result return to a list<> of BLL.BLLPayAndReceive</returns>
        public List<BLL.BLLPayAndReceive> FetchByField(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[PayAndReceive].[" + fieldName + "] = N'" + value.ToString() + "'"));

                List<BLL.BLLPayAndReceive> result = new List<BLL.BLLPayAndReceive>();
                using (IDataReader dataReader = this.Command.ExecuteReader())
                {
                    result = InitialObjectsFromReader(dataReader);
                }
                if (controlConnection)
                    this.CommitTransaction();

                return (result);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by field name that field name value is this vlaue
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <param name="payAndReceiveDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchByField(string fieldName, object value, DataTable payAndReceiveDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspPayAndReceiveFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", "[PayAndReceive].[" + fieldName + "] = N'" + value.ToString() + "'"));


                sqlDataAdapter.Fill(payAndReceiveDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by condition 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="condition" Type="string"></param>
        /// <param name="payAndReceiveDT" Type="DataTable"></param>
        /// <return>this type is void but return value to DataTable </return>
        public void FetchAllByCondition(string condition, DataTable payAndReceiveDT)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(this.Command);
            this.Command.CommandText = "uspPayAndReceiveFetchCondition";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.Parameters.Add(new SqlParameter("@condition", condition));

                sqlDataAdapter.Fill(payAndReceiveDT);

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for FetchAll all rows from table by PayAndReceiveID 
        /// this method check connection if connection is open FetchAll all rows from table
        /// </summary>
        /// <param name="payAndReceiveID" Type="int"></param>
        /// <returns>return a object that type is BLL.BLLPayAndReceive</returns>
        public BLL.BLLPayAndReceive FetchByKey(int payAndReceiveID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveFetchByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkPayAndReceiveID = new SqlParameter();
                pkPayAndReceiveID.ParameterName = "@payAndReceiveID";
                pkPayAndReceiveID.SqlDbType = SqlDbType.Int;
                pkPayAndReceiveID.Direction = ParameterDirection.Input;
                pkPayAndReceiveID.IsNullable = false;
                pkPayAndReceiveID.Value = payAndReceiveID;
                this.Command.Parameters.Add(pkPayAndReceiveID);

                if (controlConnection)
                    this.BeginTransaction();

                BLL.BLLPayAndReceive payAndReceiveObject = new BLL.BLLPayAndReceive();

                using (IDataReader dataReader = this.Command.ExecuteReader())
                {

                    if (dataReader.Read())
                        InitialBusinessObjectsFromReader(payAndReceiveObject, dataReader);
                    else
                        payAndReceiveObject = null;
                }

                if (controlConnection)
                    this.CommitTransaction();

                return (payAndReceiveObject);
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="payAndReceiveID" Type="int"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(int payAndReceiveID)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveDeleteByPK";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                SqlParameter pkPayAndReceiveID = new SqlParameter();
                pkPayAndReceiveID.ParameterName = "@payAndReceiveID";
                pkPayAndReceiveID.SqlDbType = SqlDbType.Int;
                pkPayAndReceiveID.Direction = ParameterDirection.Input;
                pkPayAndReceiveID.IsNullable = false;
                pkPayAndReceiveID.Value = payAndReceiveID;
                this.Command.Parameters.Add(pkPayAndReceiveID);

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method is for Delete a row from table by field name that field name value is this value
        /// this method check connection if connection is open Delete a row
        /// </summary>
        /// <param name="fieldName" Type="string"></param>
        /// <param name="value" Type="object"></param>
        /// <return>this type is void and Do not return</return>
        public void Delete(string fieldName, object value)
        {
            bool controlConnection = !(this.ConnectionState == System.Data.ConnectionState.Open);
            this.Command.CommandText = "uspPayAndReceiveDeleteByField";
            this.Command.CommandType = CommandType.StoredProcedure;

            try
            {
                this.Command.Parameters.Clear();

                this.Command.Parameters.Add(new SqlParameter("@fieldName", fieldName));
                this.Command.Parameters.Add(new SqlParameter("@value", value));

                if (controlConnection)
                    this.BeginTransaction();

                this.Command.ExecuteNonQuery();

                if (controlConnection)
                    this.CommitTransaction();
            }
            catch (Exception ex)
            {
                if (controlConnection)
                    this.RollBackTransaction();
                throw (ex);
            }
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>return a list<> that value is table columns value</returns>
        private List<BLL.BLLPayAndReceive> InitialObjectsFromReader(IDataReader dataReader)
        {
            List<BLL.BLLPayAndReceive> payAndReceivesList = new List<BLL.BLLPayAndReceive>();

            int payAndReceiveIDColumn = dataReader.GetOrdinal("PayAndReceiveID");
            int drawerIDColumn = dataReader.GetOrdinal("DrawerID");
            int dateColumn = dataReader.GetOrdinal("Date");
            int descriptionColumn = dataReader.GetOrdinal("Description");
            int typeColumn = dataReader.GetOrdinal("Type");

//#if DEBUG
//#warning Question?
//#endif
//            //Is it right to create Drawer here? If DALPayAndreceive object deleted what happen to drawer object?
//            if (payAndReceiveObject.DrawerObj == null)
//            {
//                payAndReceiveObject.DrawerObj = new BLL.BLLDrawer();
//            }

            while (dataReader.Read())
            {
                BLL.BLLPayAndReceive payAndReceiveObject = new BLL.BLLPayAndReceive();

                payAndReceiveObject.PayAndReceiveID = dataReader.GetInt32(payAndReceiveIDColumn);
                payAndReceiveObject.DrawerID = dataReader.GetInt32(drawerIDColumn);
                payAndReceiveObject.Date = dataReader.GetDateTime(dateColumn);
                payAndReceiveObject.Description = dataReader.GetString(descriptionColumn);

                //1 means it is Pay type
                //0 means it is Receive type
                bool bType = dataReader.GetBoolean(typeColumn);
                if (bType)
                {
                    payAndReceiveObject.Type = BLL.DocumentType.Pay;
                }

                else
                {
                    payAndReceiveObject.Type = BLL.DocumentType.Receive;
                }

                payAndReceivesList.Add(payAndReceiveObject);
            }
            return (payAndReceivesList);
        }

        /// <summary>
        /// this method give columns value by number column
        /// </summary>
        /// <param name="payAndReceiveObject" Type="BLL.BLLPayAndReceive"></param>
        /// <param name="dataReader" Type="IDataReader"></param>
        /// <returns>this method is void but return a object of BLL.BLLBank that value is table columns value</returns>
        private void InitialBusinessObjectsFromReader(BLL.BLLPayAndReceive payAndReceiveObject, IDataReader dataReader)
        {
            int payAndReceiveIDColumn = dataReader.GetOrdinal("PayAndReceiveID");
            int drawerIDColumn = dataReader.GetOrdinal("DrawerID");
            int dateColumn = dataReader.GetOrdinal("Date");
            int descriptionColumn = dataReader.GetOrdinal("Description");
            int typeColumn = dataReader.GetOrdinal("Type");

//#if DEBUG
//#warning Question?
//#endif
//            //Is it right to create Drawer here? If DALPayAndreceive object deleted what happen to drawer object?
//            if (payAndReceiveObject.DrawerObj == null)
//            {
//                payAndReceiveObject.DrawerObj = new BLL.BLLDrawer();
//            }

            payAndReceiveObject.PayAndReceiveID = dataReader.GetInt32(payAndReceiveIDColumn);
            payAndReceiveObject.DrawerID = dataReader.GetInt32(drawerIDColumn);
            payAndReceiveObject.Date = dataReader.GetDateTime(dateColumn);
            payAndReceiveObject.Description = dataReader.GetString(descriptionColumn);
            
            //1 means it is Pay type
            //0 means it is Receive type
            bool bType = dataReader.GetBoolean(typeColumn);
            if (bType)
            {
                payAndReceiveObject.Type = BLL.DocumentType.Pay;
            }

            else
            {
                payAndReceiveObject.Type = BLL.DocumentType.Receive;
            }
            

        }
    }
}
